package fuza.android.thetrafficsigns;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {

	private static String DB_PATH = "/data/data/fuza.android.thetrafficsigns/databases/";

	private static final String DATABASE_NAME = "trafficsigns.db";
	private static final int DATABASE_VERSION = 2;

	// tabel peraturan
	private static final String TABEL_PERATURAN = "tb_peraturan";
	private static final String KEY_ID_PELAKU = "id";
	private static final String KEY_KODE_PELAKU = "kode";
	private static final String KEY_PELAKU = "pelaku";
	private static final String KEY_ISI_PELAKU = "isi";
	
	// tabel rambu
	private static final String TABEL_RAMBU = "tb_rambu";
	private static final String KEY_ID_RAMBU = "id";
	private static final String KEY_GAMBAR = "n_gambar";
	private static final String KEY_DESC_RAMBU = "desc_rambu";
	private static final String KEY_JENIS_RAMBU = "jenis_rambu";

	private Context myContext;
	SQLiteDatabase db;

	public DBHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		myContext = context;
	}

	public void createDataBase() throws IOException {
		if (DataBaseisExist()) {
			// tidk melakukan apapun, db telah ada

		} else {
			// dengan memanggil method ini, db kosong akan dibuat sehingga dapat
			// ditulisi oleh
			// database aplikasi
			this.getReadableDatabase();

			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}

	}

	private boolean DataBaseisExist() {

		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DATABASE_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {

			// database tidak ada

		}
		if (checkDB != null) {
			checkDB.close();
		}

		if (checkDB != null)
			return true;
		else
			return false;
	}

	private void copyDataBase() throws IOException {

		// buka lokal database sebagai input stream
		InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

		// Path untuk pembuatan db
		String outFileName = DB_PATH + DATABASE_NAME;

		// buka db kosong sebagai output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes dari inputfile ke outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	private SQLiteDatabase sqliteDBInstance = null;

	public void openDB() throws SQLException {
		Log.i("openDB", "Checking sqliteDBInstance...");
		if (this.sqliteDBInstance == null) {
			Log.i("openDB", "Creating sqliteDBInstance...");
			this.sqliteDBInstance = this.getWritableDatabase();
		}
	}
	
	public Rambu getDetailS(String id) {
		Rambu ram = new Rambu();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT " + "*" + " FROM " + TABEL_RAMBU
				+ " WHERE " + KEY_ID_RAMBU + "=" + id, null);
		if (cur != null)
			cur.moveToFirst();
		ram.setN_gambar(cur.getString(cur.getColumnIndex(KEY_GAMBAR)));
		ram.setDesc_rambu(cur.getString(cur.getColumnIndex(KEY_DESC_RAMBU)));
		ram.setJenis_rambu(cur.getString(cur.getColumnIndex(KEY_JENIS_RAMBU)));
		cur.close();
		db.close();
		return ram;
	}
	
	public Rambu getJenis(String jenis_rambu) {
		Rambu p = new Rambu();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT " + KEY_GAMBAR + " FROM " + TABEL_RAMBU
				+ " WHERE " + KEY_JENIS_RAMBU + "=" + jenis_rambu, null);
		if (cur != null)
			cur.moveToFirst();
		p.setN_gambar(cur.getString(cur.getColumnIndex(KEY_GAMBAR)));
		p.setDesc_rambu(cur.getString(cur.getColumnIndex(KEY_DESC_RAMBU)));
		cur.close();
		db.close();
		return p;
	}

	public Laws getLawsById(String id) {
		Laws p = new Laws();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("SELECT " + "*" + " FROM " + TABEL_PERATURAN
				+ " WHERE " + KEY_ID_PELAKU + "=" + id, null);
		if (cur != null)
			cur.moveToFirst();
		p.setKode_pelaku(cur.getString(cur.getColumnIndex(KEY_KODE_PELAKU)));
		p.setN_pelaku(cur.getString(cur.getColumnIndex(KEY_PELAKU)));
		p.setIsi_peraturan(cur.getString(cur.getColumnIndex(KEY_ISI_PELAKU)));
		cur.close();
		db.close();
		return p;
	}

	public List<Rambu> selectLaws() {
	
		List<Rambu> rambu = new ArrayList<Rambu>();
		SQLiteDatabase db = this.getWritableDatabase();
		String Select = "SELECT * FROM " + TABEL_RAMBU;
		Cursor cur = db.rawQuery(Select, null);
		if (cur.moveToFirst()){
			do {
				Rambu r = new Rambu();
				r.setId(cur.getInt(cur.getColumnIndex(KEY_ID_RAMBU)));
				r.setN_gambar(cur.getString(cur.getColumnIndex(KEY_GAMBAR)));
				r.setJenis_rambu(cur.getString(cur
						.getColumnIndex(KEY_JENIS_RAMBU)));
				r.setDesc_rambu(cur.getString(cur
						.getColumnIndex(KEY_DESC_RAMBU)));
				rambu.add(r);
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		return rambu;

	}
	
	public Cursor getAllGambar(){
		Cursor c = db.query(TABEL_RAMBU, new String[] {KEY_ID_RAMBU, KEY_GAMBAR, KEY_DESC_RAMBU, KEY_JENIS_RAMBU},null,null,null,null,null);
		return c;
		}

}
